var express = require('express');
var router = express.Router();

/* GET home page. */
router.get('/list', function (req, res, next) {
    res.send('list page')
});

//引入配置和数据库操作
const {
    API_URL
} = require('../utils/config')
const Query = require('../utils/db')

//接口
//列表页-分类-地区
router.get('/api/areas', async (req, res, next) => {

    let {
        err,
        result
    } = await Query(`select * from e_area`)
    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (result.length == 0) {
        next({
            msg: "无数据",
        })
        return;
    }
    next({
        msg: "OK",
        status: 200,
        result,
    })
})

//列表页-分类-年级
router.get('/api/grades', async (req, res, next) => {

    let {
        err,
        result
    } = await Query(`select * from e_grade`)
    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (result.length == 0) {
        next({
            msg: "无数据",
        })
        return;
    }
    next({
        msg: "OK",
        status: 200,
        result,
    })
})

//列表页-分类-学科
router.get('/api/subjects', async (req, res, next) => {
    let {
        err,
        result
    } = await Query(`select * from e_subject`)
    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (result.length == 0) {
        next({
            msg: "无数据",
        })
        return;
    }
    next({
        msg: "OK",
        status: 200,
        result,
    })
})

//列表页-分类-考试类别
router.get('/api/cates', async (req, res, next) => {
    let {
        err,
        result
    } = await Query(`select * from e_category`)
    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (result.length == 0) {
        next({
            msg: "无数据",
        })
        return;
    }
    next({
        msg: "OK",
        status: 200,
        result,
    })
})

//列表页-课程列表
router.get('/api/courses', async (req, res, next) => {
    let {
        pageSize = 5, page = 1, area = '', grade = '', subject = '', category = ''
    } = req.query
    page = Number(page) //为了保证数据的统一性
    pageSize = Number(pageSize)
    let whereStr = ''

    if (area != '') {
        if (whereStr != '') {
            whereStr += ` and area_name = '${area}' `
        } else {
            whereStr = ` where area_name = '${area}' `
        }
    }

    if (grade != '') {
        if (whereStr != '') {
            whereStr += ` and grade_name = '${grade}' `
        } else {
            whereStr = ` where grade_name = '${grade}' `
        }
    }

    if (subject != '') {
        if (whereStr != '') {
            whereStr += ` and subject_name = '${subject}' `
        } else {
            whereStr = ` where subject_name = '${subject}' `
        }
    }

    if (category != '') {
        if (whereStr != '') {
            whereStr += ` and category_name = '${category}' `
        } else {
            whereStr = ` where category_name = '${category}' `
        }
    }

    let start = (page - 1) * pageSize
    let sql1 = `select * from e_course ${whereStr}`
    let sql2 = `select * from e_course ${whereStr} limit ${start},${pageSize}`

    let {
        err: err1,
        result: result1
    } = await Query(sql1)
    let {
        err,
        result
    } = await Query(sql2)

    result.forEach(val => {
        val.image_src = API_URL + '/uploads/courses/' + val.image_src;
        val.detail_imgs = API_URL + '/uploads/courses/' + val.detail_imgs;
    });

    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (result.length == 0) {
        next({
            msg: "无数据",
        })
        return;
    }

    let rows = result1.length;
    let totalPage = Math.ceil(rows / pageSize);

    pageSize = result.length
    next({
        status: 200,
        result: {
            page,
            pageSize,
            totalPage,
            rows,
            data: result,
        }
    })
})

//列表-搜索
router.get('/api/search', async (req, res, next) => {
    let {
        page = 1, pageSize = 5, keyword = ''
    } = req.query
    page = Number(page) //为了保证数据的统一性
    pageSize = Number(pageSize)

    if (keyword == '') {
        next({
            msg: '请输入关键词'
        })
        return;
    }

    //查询 课程表并设置搜索关键词
    let {
        result: result1
    } = await Query(`select * from e_course`)

    let whereStr = ''
    //如果是字符串内是空 那么where 否则or
    for (var key in result1[0]) {
        if (whereStr != '') {
            whereStr += ` or ${key} like '%${keyword}%' `
        } else {
            whereStr = `where ${key} like '%${keyword}%' `
        }
    }

    let {
        err,
        result
    } = await Query(`select * from e_course ${whereStr}`)

    let rows = result.length;
    let totalPage = Math.ceil(rows / pageSize)

    if (err) {
        next({
            msg: "请稍后再试",
        })
        return;
    }
    if (rows == 0) {
        next({
            msg: "无数据",
        })
        return;
    }

    //设置每次显示的
    let start = (page - 1) * pageSize
    let {
        result: result2
    } = await Query(`select * from e_course ${whereStr} limit ${start},${pageSize}`);
    result2.forEach(val => {
        val.image_src = API_URL + '/uploads/courses/' + val.image_src;
        val.detail_imgs = API_URL + '/uploads/courses/' + val.detail_imgs;
    });

    pageSize = result2.length //设置每次显示的页数不足时候 为result.length
    next({
        msg: "OK",
        status: 200,
        result: {
            page,
            totalPage,
            rows,
            keyword,
            data: result2,
        }
    })
})


module.exports = router;